<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Trainees extends CI_Model {

	function __construct() {
        parent::__construct();
    }

    public function broImage() {
    	return base_url() . 'img/bro.png';
    }

    public function sisImage() {
    	return base_url() . 'img/sis.png';
    }
    
    public function emailExists($email, $id) {
    	$exists = false;

    	$sql = "SELECT " .
			   "t.TraineeId " .
			   "FROM " .
			   "trainees AS t " .
			   "LEFT OUTER JOIN users AS u ON t.UserId = u.UserId " .
			   "WHERE " .
			   "(CAST(IFNULL(u.Email, '') AS char(100)) LIKE " . $this->db->escape((String) $email) . ")";

		if ($id > 0) $sql .= " AND (t.TraineeId <> " . $id . ")";

		$sql .= " UNION ALL " .
				"SELECT " .
				"CAST(IFNULL(t.TraineeId, 0) AS signed) AS `TraineeId` " .
				"FROM " .
				"users AS u " .
				"LEFT OUTER JOIN trainees AS t ON u.UserId = t.UserId  " .
				"WHERE " .
				"(u.Email LIKE " . $this->db->escape((String) $email) . ") AND " .
				"(t.UserId IS NULL)";

    	$query = $this->db->query($sql);
    	$exists = ($query->num_rows() > 0);

    	return $exists;
    }

    public function getAll() {
    	$sql = "SELECT " .
			   "t.TraineeId AS `Id`, " .
			   "CAST(CASE WHEN s.Gender LIKE 'B' THEN 'Brother' WHEN s.Gender LIKE 'S' THEN 'Sister' ELSE '' END AS char(15)) AS `Gender`, " .
			   "CONCAT(RTRIM(s.FirstName), ' ', RTRIM(s.LastName)) AS `Name`, " .
			   "DATE_FORMAT(s.BirthDate, '%d-%b-%Y') AS `BirthDate`, " .
			   "CAST(TIMESTAMPDIFF(YEAR, s.BirthDate, CURDATE()) AS signed) AS `Age`, " .
			   "c.Country AS `Nationality`, " .
			   "s.MaritalStatus, " .
			   "l.Locality, " .
			   "d.District, " .
			   "CAST('' AS char(1000)) AS `Address`, " .
			   "CAST(CONCAT(CAST(CASE WHEN RTRIM(s.PhoneNo) NOT LIKE '' THEN 'Phone : ' ELSE '' END AS char(10)), RTRIM(s.PhoneNo), CAST(CASE WHEN RTRIM(s.PhoneNo) NOT LIKE '' AND RTRIM(s.MobileNo) NOT LIKE '' THEN '   ' ELSE '' END AS char(3)), CAST(CASE WHEN RTRIM(s.MobileNo) NOT LIKE '' THEN 'Mobile : ' ELSE '' END AS char(15)), RTRIM(s.MobileNo)) AS char(250)) AS `ContactNo`, " .
			   "s.Email, " .
			   "DATE_FORMAT(s.DateBaptized, '%d-%b-%Y') AS `DateBaptized`, " .
			   "bl.Locality AS `BaptismalLocation`, " .
			   "IFNULL(ImageToHex(i.Image), '') AS `Image` " .
			   "FROM " .
			   "saints AS s " .
			   "INNER JOIN trainees AS t ON s.SaintId = t.SaintId " .
			   "INNER JOIN localities AS l ON s.LocalityId = l.LocalityId " .
			   "INNER JOIN countries AS c ON s.NationalityId = c.CountryId " .
			   "LEFT OUTER JOIN districts AS d ON s.DistrictId = d.DistrictId " .
			   "INNER JOIN localities AS bl ON s.BaptismalLocalityId = bl.LocalityId " .
			   "LEFT OUTER JOIN images AS i ON s.SaintId = i.SaintId " .
			   "ORDER BY " .
			   "s.LastName, " .
			   "s.FirstName, " .
			   "s.MiddleName";

		$query = $this->db->query($sql);
		return $query->result_array();
    }

    public function getIndividualInfo($id) {
    	$defaultImage = self::broImage();

    	$data = array(
    		'Id' => 0,
    		'Username' => '',
    		'Password' => '',
			'FirstName' => '',
			'MiddleName' => '',
			'LastName' => '',
			'BirthDate' => '01/01/1985',
			'Gender' => 'B',
			'MaritalStatus' => 'Single',
			'NationalityId' => COUNTRY_ID,
			'LocalityId' => 0,
			'DistrictId' => 0,
			'PhoneNo' => '',
			'MobileNo' => '',
			'Email' => '',
			'PassportNo' => '',
			'DateBaptized' => '01/01/1996',
			'BaptismalLocalityId' => 0,
			'Image' => $defaultImage,
			'Addresses' => array(),
			'Educations' => array()
    	);

    	$infoSql = "SELECT " .
				   "t.TraineeId AS Id, " .
				   "CAST(IFNULL(u.`Username`, '') AS char(20)) AS `Username`, " .
				   "CAST(IFNULL(u.`Password`, '') AS char(500)) AS `Password`, " .
				   "s.FirstName, " .
				   "s.MiddleName, " .
				   "s.LastName, " .
				   "DATE_FORMAT(s.BirthDate, '%m/%d/%Y') AS BirthDate, " .
				   "s.Gender, " .
				   "s.MaritalStatus, " .
				   "s.NationalityId, " .
				   "s.LocalityId, " .
				   "s.DistrictId, " .
				   "s.PhoneNo, " .
				   "s.MobileNo, " .
				   "s.Email, " .
				   "s.PassportNo, " .
				   "DATE_FORMAT(s.DateBaptized, '%m/%d/%Y') AS DateBaptized," .
				   "s.BaptismalLocalityId, " .
				   "IFNULL(ImageToHex(i.Image), CASE WHEN s.Gender LIKE 'B' THEN " . $this->db->escape((String) self::broImage()) . " ELSE " . $this->db->escape((String) self::sisImage()) . " END) AS `Image` " .
				   "FROM " .
				   "saints AS s " .
				   "INNER JOIN trainees AS t ON s.SaintId = t.SaintId " .
				   "LEFT OUTER JOIN images AS i ON s.SaintId = i.SaintId " .
				   "LEFT OUTER JOIN `users` AS u ON t.`UserId` = u.`UserId` " .
				   "WHERE " .
				   "t.TraineeId = " . $id;

		$infoQuery = $this->db->query($infoSql);
		
		if ($infoQuery->num_rows() > 0) {
			$info = $infoQuery->row_array();

			$addressSql = "SELECT " .
						  "a.AddressId AS Id, " .
						  "a.SaintId, " .
						  "a.UnitNo, " .
						  "a.Street, " .
						  "a.Subdivision, " .
						  "a.Area, " .
						  "a.LocalityId, " .
						  "a.CountryId " .
						  "FROM " .
						  "saintaddresses AS a " .
						  "INNER JOIN saints AS s ON s.SaintId = a.SaintId " .
						  "INNER JOIN trainees AS t ON s.SaintId = t.SaintId " .
						  "WHERE " .
						  "t.TraineeId = " . $id . " " .
						  "ORDER BY " .
						  "Id";

			$addressQuery = $this->db->query($addressSql);
			$address = $addressQuery->result_array();

			$educationSql = "SELECT " .
							"e.EducationId AS Id, " .
							"e.EducationDegreeId AS `EducationalDegreeId`, " .
							"e.School, " .
							"e.Course, " .
							"e.YearAttended, " .
							"e.MonthAttended, " .
							"e.DayAttended, " .
							"e.YearGraduated, " .
							"e.MonthGraduated, " .
							"e.DayGraduated " .
							"FROM " .
							"educations AS e " .
							"INNER JOIN saints AS s ON e.SaintId = s.SaintId " .
							"INNER JOIN trainees AS t ON s.SaintId = t.SaintId " .
							"WHERE " .
							"t.TraineeId = " . $id . " " .
							"ORDER BY " .
							"Id";

			$educationQuery = $this->db->query($educationSql);
			$education = $educationQuery->result_array();
	
			$data = array(
				'Id' => $info['Id'],
				'Username' => $info['Username'],
				'Password' => (trim($info['Password']) != ''? $this->encrypt->decode($info['Password']) : ''),
				'FirstName' => $info['FirstName'],
				'MiddleName' => $info['MiddleName'],
				'LastName' => $info['LastName'],
				'BirthDate' => $info['BirthDate'],
				'Gender' => $info['Gender'],
				'MaritalStatus' => $info['MaritalStatus'],
				'NationalityId' => $info['NationalityId'],
				'LocalityId' => $info['LocalityId'],
				'DistrictId' => $info['DistrictId'],
				'PhoneNo' => $info['PhoneNo'],
				'MobileNo' => $info['MobileNo'],
				'Email' => $info['Email'],
				'PassportNo' => $info['PassportNo'],
				'DateBaptized' => $info['DateBaptized'],
				'BaptismalLocalityId' => $info['BaptismalLocalityId'],
				'Image' => $info['Image'],
				'Addresses' => $address,
				'Educations' => $education
			);
		}
		
    	return $data;
    }

    public function getPaginatedList($page, $rows) {
    	$data = array();

    	$sql = "SELECT " .
			   "t.TraineeId AS `Id`, " .
			   "CAST(IFNULL(u.Username, '') AS char(20)) AS `Username`, " .
			   "CAST(IFNULL(u.`Password`, '') AS char(500)) AS `Password`, " .
			   "s.SaintId, " .
			   "s.FirstName, " .
			   "s.MiddleName, " .
			   "s.LastName, " .
			   "DATE_FORMAT(s.BirthDate, '%d-%b-%Y') AS `BirthDate`, " .
			   "CAST(CASE WHEN s.Gender LIKE 'S' THEN 'Sister' ELSE 'Brother' END AS char(10)) AS `Gender`, " .
			   "s.MaritalStatus, " .
			   "c.Country, " .
			   "l.Locality, " .
			   "d.District, " .
			   "s.PhoneNo, " .
			   "s.MobileNo, " .
			   "s.Email, " .
			   "s.PassportNo, " .
			   "DATE_FORMAT(s.DateBaptized, '%d-%b-%Y') AS `DateBaptized`, " .
			   "bl.Locality AS `BaptismalLocality`, " .
			   "IFNULL(ImageToHex(i.Image), '') AS `Image` " .
			   "FROM " .
			   "saints AS s " .
			   "INNER JOIN trainees AS t ON s.SaintId = t.SaintId " .
			   "LEFT OUTER JOIN images AS i ON s.SaintId = i.SaintId " .
			   "INNER JOIN countries AS c ON s.NationalityId = c.CountryId " .
			   "INNER JOIN localities AS l ON s.LocalityId = l.LocalityId " .
			   "INNER JOIN districts AS d ON s.DistrictId = d.DistrictId " .
			   "INNER JOIN localities AS bl ON s.BaptismalLocalityId = bl.LocalityId " .
			   "LEFT OUTER JOIN `users` AS u ON t.UserId = u.UserId " .
			   "ORDER BY " .
			   "s.LastName, " .
			   "s.FirstName, " .
			   "s.MiddleName " .
			   "LIMIT " .
			   (($page * $rows) - $rows) .  ", " . $rows;

		$query = $this->db->query($sql);
		if ($query->num_rows() > 0) {
			$result = $query->result_array();

			for ($i = 0; $i < count($result); $i++) {
				$row = $result[$i];

				$sql = "SELECT " .
					   "sa.AddressId AS `Id`, " .
					   "sa.UnitNo, " .
					   "sa.Street, " .
					   "sa.Subdivision, " .
					   "sa.Area, " .
					   "c.Country, " .
					   "l.Locality " .
					   "FROM " .
					   "saintaddresses AS sa " .
					   "INNER JOIN localities AS l ON sa.LocalityId = l.LocalityId " .
					   "INNER JOIN countries AS c ON c.CountryId = sa.CountryId " .
					   "WHERE " .
					   "sa.SaintId = " . $row['SaintId'] . " " .
					   "ORDER BY " .
					   "`Id`";

				$query = $this->db->query($sql);
				$addresses = array();

				if ($query->num_rows() > 0) $addresses = $query->result_array();

				$sql = "SELECT " .
					   "e.EducationId AS `Id`, " .
					   "ed.EducationalDegree, " .
					   "e.School, " .
					   "e.Course, " .
					   "e.YearAttended, " .
					   "e.MonthAttended, " .
					   "e.DayAttended, " .
					   "e.YearGraduated, " .
					   "e.MonthGraduated, " .
					   "e.DayGraduated " .
					   "FROM " .
					   "educations AS e " .
					   "INNER JOIN educationaldegrees AS ed ON e.EducationDegreeId = ed.EducationalDegreeId " .
					   "WHERE " .
					   "e.SaintId = " . $row['SaintId'] . " " .
					   "ORDER BY " .
					   "`Id`";

				$query = $this->db->query($sql);
				$educations = array();

				if ($query->num_rows() > 0) $educations = $query->result_array();

				array_push($data, array(
					'Id' => $row['Id'],
					'Username' => $row['Username'],
					'Password' => (trim($row['Password']) != ''? $this->encrypt->decode($row['Password']) : ''),
					'SaintId' => $row['SaintId'],
					'FirstName' => $row['FirstName'],
					'MiddleName' => $row['MiddleName'],
					'LastName' => $row['LastName'],
					'BirthDate' => $row['BirthDate'],
					'Gender' => $row['Gender'],
					'MaritalStatus' => $row['MaritalStatus'],
					'Country' => $row['Country'],
					'Locality' => $row['Locality'],
					'District' => $row['District'],
					'PhoneNo' => $row['PhoneNo'],
					'MobileNo' => $row['MobileNo'],
					'Email' => $row['Email'],
					'PassportNo' => $row['PassportNo'],
					'DateBaptized' => $row['DateBaptized'],
					'BaptismalLocality' => $row['BaptismalLocality'],
					'Addresses' => $addresses,
					'Educations' => $educations,
					'Image' => $row['Image']
				));
			}
		}

    	return $data;
    }

    public function recordCount() {
    	$count = 0;

    	$sql = "SELECT " .
			   "COUNT(s.SaintId) AS `Count` " .
			   "FROM " .
			   "saints AS s " .
			   "INNER JOIN trainees AS t ON s.SaintId = t.SaintId";

		$query = $this->db->query($sql);
		if ($query->num_rows() > 0) {
			$row = $query->row_array();
			$count = $row['Count'];
			if (!isset($count)) $count = 0;
		}

    	return $count;
    }

    public function save($info) {
    	$udpates = array(
    		'saved' => false,
    		'trainee' => array(),
    		'addresses' => array(),
    		'educations' => array()
    	);

    	$withUpdates = false;

    	if (isset($info->trainee)) {
    		$trainee = $info->trainee;
    		if ($trainee->RowState != 'unchanged') $withUpdates = true;
    	}
    	
    	if (!$withUpdates) {
    		if (isset($info->addresses)) {
	    		if (count($info->addresses) > 0) $withUpdates = true;
	    	}	
    	}
    	
    	if (!$withUpdates) {
    		if (isset($info->educations)) {
	    		if (count($info->educations) > 0) $withUpdates = true;
	    	}	
    	}
    	
    	if ($withUpdates) {
    		$this->db->trans_begin();

    		$saintId = 0;
    		$traineeId = $trainee->Id;
    		$traineesUpdate = array();
    		$addressesUpdate = array();
	    	$educationsUpdate = array();

    		if (isset($info->trainee)) {
	    		$trainee = $info->trainee;

	    		$sql = '';
	    		$params = array();

	    		if ($trainee->RowState == 'added') {
	    			$sql = "INSERT INTO `saints` " .
		    			   "(`FirstName`, `MiddleName`, `LastName`, `BirthDate`, `Gender`, `MaritalStatus`, `NationalityId`, `LocalityId`, `DistrictId`, `PhoneNo`, `MobileNo`, `Email`, `PassportNo`, `DateBaptized`, `BaptismalLocalityId`) " . 
		    			   "VALUES " .
		    			   "(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

		    		$params = array(
		    			$trainee->FirstName, $trainee->MiddleName, $trainee->LastName, $trainee->BirthDate, $trainee->Gender,
		    			$trainee->MaritalStatus, $trainee->NationalityId, $trainee->LocalityId, $trainee->DistrictId,
		    			$trainee->PhoneNo, $trainee->MobileNo, $trainee->Email, $trainee->PassportNo,
		    			$trainee->DateBaptized, $trainee->BaptismalLocalityId
		    		);
	    		}
	    		else if ($trainee->RowState == 'modified') {
	    			$sql = "UPDATE `saints` SET " .
	    				   "`FirstName` = ?, `MiddleName` = ?, `LastName` = ?, `BirthDate` = ?, `Gender` = ?, `MaritalStatus` = ?, `NationalityId` = ?, `LocalityId` = ?, `DistrictId` = ?, `PhoneNo` = ?, `MobileNo` = ?, `Email` = ?, `PassportNo` = ?, `DateBaptized` = ?, `BaptismalLocalityId` = ? " .
	    				   "WHERE " . 
	    				   "(`SaintId` = CAST(IFNULL(GetSaintIdByTraineeId(?), 0) AS signed))";

	    			$params = array(
		    			$trainee->FirstName, $trainee->MiddleName, $trainee->LastName, $trainee->BirthDate, $trainee->Gender,
		    			$trainee->MaritalStatus, $trainee->NationalityId, $trainee->LocalityId, $trainee->DistrictId,
		    			$trainee->PhoneNo, $trainee->MobileNo, $trainee->Email, $trainee->PassportNo,
		    			$trainee->DateBaptized, $trainee->BaptismalLocalityId, $trainee->Id
		    		);
	    		}
	    		else if ($trainee->RowState == 'deleted') {
	    			$sql = "DELETE FROM `saints` WHERE (`SaintId` = CAST(IFNULL(GetSaintIdByTraineeId(?), 0) AS signed))";
	    			$params = array($trainee->Id);
	    		}
	    		else { }

	    		if (trim($sql) != '') {
	    			if ($trainee->RowState == 'deleted') {
	    				$delSql = "DELETE FROM `users` WHERE (`UserId` IN(SELECT t.`UserId` FROM `trainees` AS t WHERE (t.`TraineeId` = ?)))";
	    				$this->db->query($delSql, $params);
	    			}

	    			$this->db->query($sql, $params);
	    		}

	    		$sql = '';
	    		$params = array();

	    		if ($trainee->RowState == 'added') {
	    			$sql = "SELECT CAST(IFNULL(LAST_INSERT_ID(), 0) AS signed) AS `Id`";
	    		}
	    		else { 
	    			if ($trainee->RowState != 'deleted') {
		    			$sql = "SELECT CAST(IFNULL(GetSaintIdByTraineeId(?), 0) AS signed) AS `Id`";
		    			$params = array($trainee->Id);	
		    		}
	    		}

	    		if (trim($sql) != '') {
	    			$query = null;

	    			if (count($params) > 0) $query = $this->db->query($sql, $params);
	    			else $query = $this->db->query($sql);

	    			if ($query->num_rows() > 0) {
	    				$row = $query->row_array();
	    				$saintId = $row['Id'];
	    			}
	    		}

	    		if ($saintId > 0) {
	    			$sql = ''; $params = array();

	    			$userId = 0;

	    			if (trim($trainee->Username) != '' &&
    					trim($trainee->Password) != '') {
    					$userId = 0;

    					$sql = "SELECT `UserId` FROM `trainees` AS t WHERE (`TraineeId` = ?)";
    					$params = array($trainee->Id);

    					$query = $this->db->query($sql, $params);
    					
    					$withUser = false;
    					$sql = ''; $params = array();

    					if ($query->num_rows() > 0) {
    						$row = $query->row_array();
    						$userId = $row['UserId'];
    						$withUser = ($userId > 0);
    					}

    					if (!$withUser) {
    						$sql = "INSERT INTO `users` " .
    							   "(`Username`, `Password`, `FirstName`, `MiddleName`, `LastName`, `Gender`, `Role`, `Privileges`, `Email`, `DateCreated`) " .
    							   "VALUES " .
    							   "(?, ?, ?, ?, ?, ?, ?, ?, ?, NOW())";

    						$privileges = '';

    						for ($i = 0; $i < 500; $i++) $privileges .= '1';

    						$params = array(
    							$trainee->Username, $this->encrypt->encode($trainee->Password), $trainee->FirstName,
    							$trainee->MiddleName, $trainee->LastName, $trainee->Gender, $this->encrypt->encode(REGULAR_USER_ROLE),
    							$privileges, $trainee->Email
    						);
    					}
    					else {
    						$sql = "UPDATE `users` SET " .
    							   "`Username` = ?, `Password` = ?, `Email` =? " .
    							   "WHERE " .
    							   "(`UserId` = ?)";

    						$params = array(
    							$trainee->Username, $this->encrypt->encode($trainee->Password), 
    							$trainee->Email, $userId
    						);
    					}

    					if (trim($sql) != '') {
    						$this->db->query($sql, $params);

    						$sql = '';

    						if ($userId <= 0) {
	    						$sql = "SELECT CAST(IFNULL(LAST_INSERT_ID(), 0) AS signed) AS `Id`";
	    						$query = $this->db->query($sql);

	    						if ($query->num_rows() > 0) {
	    							$row = $query->row_array();
	    							$userId = $row['Id'];
	    						}
	    					}	
    					}

    					$sql = ''; $params = array();	
    				}

	    			if ($trainee->RowState == 'added') {
	    				$sql = "INSERT INTO `trainees` " . 
	    					   "(`SaintId`, `UserId`, `DateCreated`) " .
	    					   "VALUES " .
	    					   "(?, ?, NOW())";
	    				
	    				$params = array($saintId, $userId);
	    				$this->db->query($sql, $params);
	    				
	    				$sql = ''; $query = null;
	    				
	    				$sql = "SELECT CAST(IFNULL(LAST_INSERT_ID(), 0) AS signed) AS `Id`";
	    				$query = $this->db->query($sql);

	    				if ($query->num_rows() > 0) {
	    					$row = $query->row_array();
	    					$traineeId = $row['Id'];

	    					$traineesUpdate = array( 'Id' => $trainee->Id, 'GeneratedId' => $traineeId );
	    					$_SESSION[TRAINEE_ID_SESSION] = $traineeId;
	    				}
	    			}
	    			else if ($trainee->RowState == 'modified') {
	    				$sql = "UPDATE `trainees` SET " .
	    					   "`UserId` = ? " .
	    					   "WHERE " .
	    					   "(`TraineeId` = ?)";

	    				$params = array($userId, $traineeId);
	    				$this->db->query($sql, $params);
	    			}

	    			if (isset($info->addresses)) {
	    				$addresses = $info->addresses;

	    				foreach ($addresses as $address) {
	    					$sql = '';
	    					$params = array();

	    					if ($address->RowState == 'added') {
	    						$sql = "INSERT INTO `saintaddresses` " .
	    							   "(`SaintId`, `UnitNo`, `Street`, `Subdivision`, `Area`, `LocalityId`, `CountryId`, `DateCreated`) " .
	    							   "VALUES " .
	    							   "(?, ?, ?, ?, ?, ?, ?, NOW())";
	    						$params = array(
	    							$saintId, $address->UnitNo, $address->Street, $address->Subdivision, $address->Area,
	    							$address->LocalityId, $address->CountryId
	    						);
	    					}
	    					else if ($address->RowState == 'modified') {
	    						$sql = "UPDATE `saintaddresses` SET " . 
	    							   "`SaintId` = ?, `UnitNo` = ?, `Street` = ?, `Subdivision`= ?, `Area` = ?, `LocalityId` = ?, `CountryId` = ? " .
	    							   "WHERE " . 
	    							   "(`AddressId` = ?)";

	    						$params = array(
	    							$saintId, $address->UnitNo, $address->Street, $address->Subdivision, $address->Area,
	    							$address->LocalityId, $address->CountryId, $address->Id
	    						);	
	    					}
	    					else if ($address->RowState == 'deleted') {
	    						$sql = "DELETE FROM `saintaddresses` WHERE (`AddressId` = ?)";
	    						$params = array($address->Id);
	    					}
	    					else { }

	    					if (trim($sql) != '') $this->db->query($sql, $params);

	    					$sql = ''; $params = array();

	    					if ($address->RowState == 'added') {
	    						$id = $address->Id;
	    						$query = $this->db->query("SELECT LAST_INSERT_ID() AS `Id`");
	    						
	    						if ($query->num_rows() > 0) {
	    							$row = $query->row_array();
	    							$id = $row['Id'];
	    						}

	    						array_push($addressesUpdate, array( 'Id' => $address->Id, 'GeneratedId' => $id));
	    					}	
	    				}
	    			}

	    			if (isset($info->educations)) {
	    				$educations = $info->educations;

	    				foreach ($educations as $education) {
	    					$sql = '';
	    					$params = array();

	    					if ($education->RowState == 'added') {
	    						$sql = "INSERT INTO `educations` " .
	    							   "(`SaintId`, `EducationDegreeId`, `School`, `Course`, `YearAttended`, `MonthAttended`, `YearGraduated`, `MonthGraduated`) " .
	    							   "VALUES " .
	    							   "(?, ?, ?, ?, ?, ?, ?, ?)";

	    						$params = array(
	    							$saintId, $education->EducationDegreeId, $education->School, $education->Course,
	    							$education->YearAttended, $education->MonthAttended, 
	    							$education->YearGraduated, $education->MonthGraduated
	    						);
	    					}
	    					else if ($education->RowState == 'modified') {
	    						$sql = "UPDATE `educations` SET " . 
	    							   "`SaintId` = ?, `EducationDegreeId` = ?, `School` = ?, `Course` = ?, `YearAttended` = ?, `MonthAttended` = ?, `YearGraduated` = ?, `MonthGraduated` = ? " .
	    							   "WHERE " . 
	    							   "(`EducationId` = ?)";

	    						$params = array(
	    							$saintId, $education->EducationDegreeId, $education->School, $education->Course,
	    							$education->YearAttended, $education->MonthAttended, 
	    							$education->YearGraduated, $education->MonthGraduated,
	    							$education->Id
	    						);
	    					}
	    					else if ($education->RowState == 'deleted') {
	    						$sql = "DELETE FROM `educations` WHERE (`EducationId` = ?)";
	    						$params = array($education->Id);
	    					}
	    					else { }

	    					if (trim($sql) != '') $this->db->query($sql, $params);

	    					if ($education->RowState == 'added') {
	    						$id = $education->Id;
	    						$query = $this->db->query("SELECT LAST_INSERT_ID() AS `Id`");
	    						
	    						if ($query->num_rows() > 0) {
	    							$row = $query->row_array();
	    							$id = $row['Id'];
	    						}

	    						array_push($educationsUpdate, array( 'Id' => $education->Id, 'GeneratedId' => $id));
	    					}
	    				}
	    			}

	    		}
	    	}

	    	$saved = true;

	    	if ($this->db->trans_status() === FALSE) {
	            $this->db->trans_rollback();
	            $saved = false;
	        }
	        else {
	            $this->db->trans_commit();
	        }

	        $udpates = array(
	    		'saved' => $saved,
	    		'trainee' => $traineesUpdate,
	    		'addresses' => $addressesUpdate,
	    		'educations' => $educationsUpdate
	    	);
    	}

    	return $udpates;
    }

    public function saveImage($id, $image) {
    	$saved = true;

    	$sql = "SELECT " .
			   "i.ImageId AS `Id` " .
			   "FROM " .
			   "images AS i " .
			   "WHERE " .
			   "i.SaintId = CAST(GetSaintIdByTraineeId(" . $id . ") AS signed) " . 
			  "LIMIT 1";

		$imageId = 0;

		$query = $this->db->query($sql);
		if ($query->num_rows() > 0) {
			$row = $query->row_array();
			$imageId = $row['Id'];
		}

		$sql = ""; $params = array();
		$this->db->trans_begin();

		if ($imageId <= 0) {
			$sql = "INSERT INTO `images` " .
				   "(`SaintId`, `Image`) " .
				   "VALUES " .
				   "(CAST(GetSaintIdByTraineeId(?) AS signed), ?)";

			$params = array($id, $image);
		}
		else {
			$sql = "UPDATE `images` SET " .
				   "`Image` = ? " .
				   "WHERE " .
				   "(`ImageId` = ?)";

			$params = array($image, $imageId);
		}

		$this->db->query($sql, $params);

		if ($this->db->trans_status() === FALSE) {
            $this->db->trans_rollback();
            $saved = false;
        }
        else {
            $this->db->trans_commit();
        }

    	return $saved;
    }

    public function search($value) {
    	$result = array();

    	$search = str_replace("'", "''", $value);

    	$sql = "SELECT " .
			   "t.TraineeId AS `Id`, " .
			   "s.SaintId, " .
			   "s.FirstName, " .
			   "s.MiddleName, " .
			   "s.LastName, " .
			   "DATE_FORMAT(s.BirthDate, '%d-%b-%Y') AS `BirthDate`, " .
			   "CAST(CASE WHEN s.Gender LIKE 'S' THEN 'Sister' ELSE 'Brother' END AS char(10)) AS `Gender`, " .
			   "s.MaritalStatus, " .
			   "c.Country, " .
			   "l.Locality, " .
			   "d.District, " .
			   "s.PhoneNo, " .
			   "s.MobileNo, " .
			   "s.Email, " .
			   "s.PassportNo, " .
			   "DATE_FORMAT(s.DateBaptized, '%d-%b-%Y') AS `DateBaptized`, " .
			   "bl.Locality AS `BaptismalLocality`, " .
			   "IFNULL(ImageToHex(i.Image), '') AS `Image` " .
			   "FROM " .
			   "saints AS s " .
			   "INNER JOIN trainees AS t ON s.SaintId = t.SaintId " .
			   "LEFT OUTER JOIN images AS i ON s.SaintId = i.SaintId " .
			   "INNER JOIN countries AS c ON s.NationalityId = c.CountryId " .
			   "INNER JOIN localities AS l ON s.LocalityId = l.LocalityId " .
			   "INNER JOIN districts AS d ON s.DistrictId = d.DistrictId " .
			   "INNER JOIN localities AS bl ON s.BaptismalLocalityId = bl.LocalityId " .
			   "WHERE " .
			   "(CONCAT(RTRIM(s.FirstName), ' ', RTRIM(s.MiddleName), ' ', RTRIM(s.LastName)) LIKE '%" . str_replace(" ", "%') AND (CONCAT(RTRIM(s.FirstName), ' ', RTRIM(s.MiddleName), ' ', RTRIM(s.LastName)) LIKE '%", $search) . "%') " .
			   "ORDER BY " .
			   "s.FirstName, " .
			   "s.MiddleName, " . 
			   "s.LastName " .
			   "LIMIT 15";

		$query = $this->db->query($sql);
		if ($query->num_rows() > 0) $result = $query->result_array();

    	return $result;
    }

 }